var express = require('express');
var router = express.Router();
// 导入数据库连接对象
var db = require("../db/db.js");

var xlsx = require('node-xlsx').default;
var path = require("path");
var fs = require("fs");
var dayjs = require('dayjs')

// 1.添加数据
router.post("/add",function(req,res,next){
  //1。获取id
  var body = req.body;
  // console.log(body)

  if(!req.body){
      res.json({msg:"缺少参数",code:-1})
  }
  //2.查询id对应的内容
  var sql = "insert into yx_student (`class_name`,`ctime` ,`uname`,`collage` ,`fresh` ,`improve` ,`tel` ,`wechat` ,`work_city` ,`work` ,`is_test`,`reason` ,`salary` ,`training` ,`we_do` ,`other_company` ) "
  +"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

  var time = dayjs().unix();
  // console.log("time:"+time)
  var class_name = body.class_name;
  var uname = body.uname;
  var collage = body.collage;
  var fresh = body.fresh;
  var improve = body.improve;
  var is_test = body.is_test;
  var other_company = body.other_company;
  var reason = body.reason;
  var salary = body.salary;
  var tel =  body.tel;
  var training = body.training;
  var we_do = body.we_do;
  var wechat = body.wechat;
  var work = body.work;
  var work_city = body.work_city;

  var data = [
    class_name,//extral
    time,
    uname,
    collage,
    fresh,
    improve,
    tel,
    wechat,
    work_city,
    work,
    is_test,
    reason,
    salary,
    training,
    we_do,
    other_company
  ];
  
  // console.log("sql data:",data)
  db.conn.query(sql,data,(err,result)=>{
    if(err){
      console.log(err)
      return res.json({msg:"数据库异常"+err.message,code:-2})
    }
    // console.log(result)
    if( result.affectedRows){
      res.json({msg:"ok",code:0,data:result})
    }
  })
})

/* 2.学生列表+分页*/
router.get('/list', function(req, res, next) {
  var keywords = req.query.keywords;//关键词
  //开始查询
  var where = ' where 1 = 1 ';

  if(keywords){
    where += ` and uname like '%${keywords}%'`
  }

  let sql = `select count(*) as total from yx_student ${where}`;

  db.conn.query(sql,(err,results)=>{
    if(err){
      return;
    }
    // console.log(results)
    //
    //查询，返回的是数组
    //console.log(results,results[0].total)
    //分页参数开
    var pageSize = req.query.page_size ? req.query.page_size : 10;//每页显示条数
    var page = req.query.page ? req.query.page : 1; //当前页码
    var count_rows = results[0].total;//总记录数
    var totalPage = Math.ceil(count_rows / pageSize);
    
    //查询
    let sql =`select * from yx_student ${where}
              ORDER BY ctime DESC 
              LIMIT ${(page -1)*pageSize},${pageSize}`;
    // console.log("list sql:",sql);

    db.conn.query(sql,(err,results)=>{
      if(err){return}
      // 返回数据
      var data = {};
      data.totalPage=totalPage;//总页数
      data.count_rows = count_rows;//总记录数
      data.page = page;//当前第几页
      data.pageSize = pageSize;//每页条数
      data.keywords = keywords;//搜索的关键词
      data.list = results;//列表
      // 返回json
      res.json({code:0,msg:"ok",data});
    })
  })
  
});

//3.导出excel
router.get("/export",function(req,res,next){
  //查询所有数据
  db.conn.query("select * from yx_student",(err,result)=>{
    if(err){
      console.log("err:",err.message)
    }
    //表格 数据
    let excelData = [];
    /* 
    <th>ID</th>
    <th>录入时间</th>
    <th>姓名</th>
    <th>性别</th>
    <th>学历</th>
    <th>应届</th>
    <th>提升</th>
    <th>电话</th>
    <th>微信</th>
    <th>工作城市</th>
    <th>工作与行业</th>
    <th>是否参与测试</th>
    <th>原因</th>
    <th>期望薪资</th>
    <th>是否系统培训</th>
    <th>关于我们</th>
    <th>其他企业有了解过吗</th>
    */
    let title = [
      "序号","录入时间","谁的班级","姓名","性别","学历","应届","后期是否想提升学历","电话","微信",
      "微信","意向工作城市","意向工作方向是什么？为什么？","沟通后是否想进入测试或者新媒体行业？具体哪个行业？",
      "为什么不从事这两个行业？有什么顾虑？","毕业预期薪资？",
      "是否有意向投入培训/提升","关于我们","有没有其他机构沟通过？自己是怎么想的？",//基本
      "是否已经找到实习或者在找实习？","报名的话全款还是分期？","最大的顾虑是什么？",
      "是否愿意参加3天的训练营?","还需要了解哪些问题?","怎么样才会报名？","课程意向","转介绍电话及个数"
    ]; //表头
    excelData.push(title);
    
    //取记录
    /* 
    {
				"id": 8,
				"ctime": 1652275051,
				"uname": "1",
				"tid": 1,
				"tname": "陈老师",
				"sex": "男",
				"age": 22,
				"collage": 1,
				"fresh": 1,
				"improve": "fwf",
				"tel": "份未发",
				"wechat": " 而非我",
				"work_city": "分为",
				"work": "155",
				"is_test": "是",
				"reason": "份未发",
				"salary": "而无法",
				"training": "wx",
				"we_do": "f午饭",
				"other_company": "为约翰",
				"practice": null,
				"payment": 0,
				"worry": null,
				"training_camp": null,
				"question": null,
				"sign_up": null,
				"intention": null,
				"introduction": null,
				"class_name": null
			}
    */
    var collage = ['专科',"本科",'研究生'];
    var payment =['全款','分期'];

    result.forEach((item,index) => {
      var itemArr = [];//每次条记录生成一个数组
      itemArr.push(index+1);
      itemArr.push(dayjs(item.ctime).format("MM月DD日"));
      itemArr.push(item.tname);
      itemArr.push(item.uname);
      itemArr.push(item.sex);
      itemArr.push(collage[item.collage -1]); 
      itemArr.push(item.fresh==1? '是':'否');
      itemArr.push(item.improve==1? '是':'否');
      itemArr.push(item.tel);
      itemArr.push(item.wechat);
      itemArr.push(item.work_city);
      itemArr.push(item.work);
      itemArr.push(item.is_test);
      itemArr.push(item.reason);
      itemArr.push(item.salary);
      itemArr.push(item.training);
      itemArr.push(item.we_do);
      itemArr.push(item.other_company);
      //后续跟踪
      itemArr.push(item.practice);
      itemArr.push(payment[item.payment]);//全款或分期：0全款，1分期
      itemArr.push(item.worry);
      itemArr.push(item.training_camp);
      itemArr.push(item.question);//补充问题
      itemArr.push(item.sign_up);
      itemArr.push(item.intention);
      itemArr.push(item.introduction);
  
      excelData.push(itemArr);//添加到excel
    });
    // console.log("excelData:",excelData)
     
    //创建excel，得到原始数据
    var buffer = xlsx.build([{name: '学生意向', data: excelData}]);
    //文件路径
    var excelPath = path.resolve(__dirname,"../public/exportFiles");
    console.log(excelPath);

    var time = dayjs().format("YYYYMMDDHHmmss");
    var fileName = `意向统计_${time}.xlsx`;
    //写文件
    fs.writeFile(excelPath+"/"+fileName,buffer,'utf8',(err)=>{
      console.log("文件创建完成")
      res.download(excelPath+"/"+fileName,downerr=>{
        if(downerr){
          console.log("downerr:",downerr)
        }
        console.log("准备下载...")
      })
    }) 
    //
  })
})

//4.删除-软删除
router.delete("/delete",function(req,res,next){
  //1。获取id
  var params = req.query;
  if(!params.id){
      res.json({msg:"缺少参数",code:-1})
  }
  //2.查询id对应的博客内容
  var sql = "delete from yx_student where id=?";
  var data = [params.id];

  db.conn.query(sql,data,(err,result)=>{
    if(err){
      console.log(err)
      return res.json({msg:"数据库异常"+err.message,code:-1})
    }
    console.log(result)
    if( result.affectedRows){
      res.json({msg:"ok",code:0,data:result})
    }
  })
})

//5.修改-获取数据
router.get("/edit",function(req,res,next){
  //1。获取id
  var params = req.query;
  if(!params.id){
      res.json({msg:"缺少参数",code:-1})
  }
  //2.查询id对应的博客内容
  var sql = "select * from yx_student where id=?";
  var data = [params.id];
  db.conn.query(sql,data,(err,result)=>{
    if(err){
      console.log(err)
      return res.json({msg:"数据库异常"+err.message,code:-1})
    }
    // console.log(result,params)
    res.json({msg:"ok",code:0,data:result[0]})
  })
})

//6.修改-保存数据
router.post("/update",function(req,res,next){
  //1。获取id
  // console.log(req.body)
  if(!req.body){
      res.json({msg:"缺少参数",code:-1})
  }
  //2.查询id对应的博客内容
  /* 
  `class_name`,
  `ctime` ,
  `uname`,
  `collage` ,
  `fresh` ,
  `improve` ,
  `tel` ,
  `wechat` ,
  `work_city` ,
  `work` ,
  `is_test`,
  `reason` ,
  `salary` ,
  `training` ,
  `we_do` ,
  `other_company`
  */
  var body = req.body; //请求数据

  var sql = `update yx_student 
  set class_name = ?,
  uname = ?,
  collage = ?,
  fresh = ?,
  improve = ?,
  tel = ?,
  wechat = ?,
  work_city = ?,
  work = ?,
  is_test = ?,
  reason = ?,
  salary = ?,
  training=?,
  we_do = ?,
  other_company=?,
  practice = ?,
  payment = ?,
  worry = ?,
  training_camp = ?,
  question = ?,
  sign_up = ?,
  intention = ?,
  introduction = ?
  where id=?`;

  var class_name = body.class_name;
  var uname = body.uname;
  var collage = body.collage;
  var fresh = body.fresh;
  var improve = body.improve;
  var tel =  body.tel;
  var wechat = body.wechat;
  var work = body.work;
  var work_city = body.work_city;
  var is_test = body.is_test;
  var reason = body.reason;
  var salary = body.salary;
  var training = body.training;
  var we_do = body.we_do;
  var other_company = body.other_company;
  // 宣讲后 更新字段
  var practice = body.practice;
  var payment= body.payment;
  var worry= body.worry;
  var training_camp= body.training_camp;
  var question= body.question;
  var sign_up= body.sign_up;
  var intention= body.intention; //报名意向老师判断
  var introduction= body.id; //转介绍
  var id = body.id;//id

  var data = [
    class_name,//extral
    uname,
    collage,
    fresh,
    improve,
    tel,
    wechat,
    work_city,
    work,
    is_test,
    reason,
    salary,
    training,
    we_do,
    other_company,
    // 直播课后统计
    practice,
    payment,
    worry,
    training_camp,
    question,
    sign_up,
    intention,
    introduction,
    id //id
  ];

  db.conn.query(sql,data,(err,result)=>{
    // console.log(sql)
    if(err){
      console.log(err)
      return res.json({msg:"数据库异常"+err.message,code:-1})
    }
    console.log(result)
    if( result.affectedRows){
      res.json({msg:"ok",code:0,data:result})
    }
  })
})

module.exports = router;